package com.tisson.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.tisson.database.FormatUtil;
import com.tisson.database.StatementUtil;
import com.tisson.pojo.AcUser;
import com.tisson.pojo.Area;

public class AcUserDAO {
	private String sqlStr;

	public void save(AcUser user) {
		try {
			if (user != null) {
				sqlStr = "";
				sqlStr += "INSERT INTO ac_user(CallId,WorkerNo,Password,ExpiryDate,UserName,UserDepartment";
				sqlStr += ",UserPhone,UserEmail,UserState) VALUES('"
						+ user.getCallId() + "',";
				sqlStr += "'" + user.getWorkerNO() + "',";
				sqlStr += "'" + user.getPassword() + "',";
				sqlStr += "'" + user.getExpiryDate().toLocaleString() + "',";
				sqlStr += "'" + user.getUsername() + "',";
				sqlStr += "'" + user.getUserDepartment() + "',";
				sqlStr += "'" + user.getUserPhone() + "',";
				sqlStr += "'" + user.getUserEmail() + "',";
				sqlStr += "'" + user.getUserState() + "')";
				Statement stmt = StatementUtil.getStatement();
				stmt.executeUpdate(sqlStr);
				stmt.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public List combineQuery(String name, String depart) throws SQLException {
		List result = new ArrayList();
		if ((depart == null || depart.equals(""))
				&& (name == null || name.equals(""))) {
			sqlStr = "select * from ac_user,ac_department where UserDepartment = DepartmentId";
		} else if (depart == null || depart.equals("")) {
			sqlStr = "select * from ac_user,ac_department where UserName = '"
					+ name + "' and UserDepartment = DepartmentId";
		} else if (name == null || name.equals("")) {
			sqlStr = "select * from ac_user,ac_department where UserDepartment = '"
					+ depart + "' and UserDepartment = DepartmentId";
		} else {
			sqlStr = "select * from ac_user,ac_department where UserName = '"
					+ name + "' and UserDepartment = '" + depart
					+ "' and UserDepartment = DepartmentId";
		}
		Statement stmt = StatementUtil.getStatement();
		ResultSet rs = stmt.executeQuery(sqlStr);
		while (rs.next()) {
			AcUser ac_user = new AcUser();
			ac_user.setUserId(FormatUtil.ConvertUuid(rs.getBytes("UserId")));
			ac_user.setCallId((String) rs.getObject("CallId"));
			ac_user.setWorkerNO((String) rs.getObject("WorkerNo"));
			ac_user.setPassword((String) rs.getObject("Password"));
			ac_user.setExpiryDate((Date) rs.getObject("ExpiryDate"));
			ac_user.setUsername((String) rs.getObject("UserName"));
			ac_user.setUserDepartment((String) rs.getObject("DepName"));
			ac_user.setUserPhone((String) rs.getObject("UserPhone"));
			ac_user.setUserEmail((String) rs.getObject("UserEmail"));
			ac_user.setUserState((Integer) rs.getObject("UserState"));
			result.add(ac_user);
		}
		rs.close();
		stmt.close();
		return result;
	}

	public AcUser findById(String id) throws SQLException{
		ResultSet rs;
		AcUser ac_user = new AcUser();
		sqlStr = "";
		sqlStr += "SELECT * FROM ac_user ";
		sqlStr += "WHERE UserId = '" + id + "'";
		Statement stmt = StatementUtil.getStatement();
		rs = stmt.executeQuery(sqlStr);
		while (rs.next()) {
			ac_user.setUserId(FormatUtil.ConvertUuid(rs.getBytes("UserId")));
			ac_user.setCallId((String) rs.getObject("CallId"));
			ac_user.setWorkerNO((String) rs.getObject("WorkerNo"));
			ac_user.setPassword((String) rs.getObject("Password"));
			ac_user.setExpiryDate((Date) rs.getObject("ExpiryDate"));
			ac_user.setUsername((String) rs.getObject("UserName"));
			ac_user.setUserDepartment(FormatUtil.ConvertUuid(rs.getBytes("UserDepartment")));
			ac_user.setUserPhone((String) rs.getObject("UserPhone"));
			ac_user.setUserEmail((String) rs.getObject("UserEmail"));
			ac_user.setUserState((Integer) rs.getObject("UserState"));
		}
		rs.close();
		stmt.close();
		return ac_user;
	}
	
	public List findByUserDepartment(String userDepartment) {
		ResultSet rs;
		List result = new ArrayList();
		sqlStr = "";
		sqlStr += "SELECT * FROM ac_user ";
		sqlStr += "WHERE UserDepartment = '" + userDepartment + "'";
		try {
			Statement stmt = StatementUtil.getStatement();
			rs = stmt.executeQuery(sqlStr);
			while (rs.next()) {
				AcUser ac_user = new AcUser();
				ac_user.setUserId(FormatUtil.ConvertUuid(rs.getBytes("UserId")));
				ac_user.setCallId((String) rs.getObject("CallId"));
				ac_user.setWorkerNO((String) rs.getObject("WorkerNo"));
				ac_user.setPassword((String) rs.getObject("Password"));
				ac_user.setExpiryDate((Date) rs.getObject("ExpiryDate"));
				ac_user.setUsername((String) rs.getObject("UserName"));
				ac_user.setUserDepartment(FormatUtil.ConvertUuid(rs.getBytes("UserDepartment")));
				ac_user.setUserPhone((String) rs.getObject("UserPhone"));
				ac_user.setUserEmail((String) rs.getObject("UserEmail"));
				ac_user.setUserState((Integer) rs.getObject("UserState"));
				result.add(ac_user);
			}
			rs.close();
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	public void update(AcUser user) {
		sqlStr = "";
		sqlStr += "UPDATE ac_user SET ";
		sqlStr += "CallId = '" + user.getCallId() + "',";
		sqlStr += "WorkerNo = '" + user.getWorkerNO() + "',";
		sqlStr += "Password = '" + user.getPassword() + "',";
		sqlStr += "ExpiryDate = '" + user.getExpiryDate().toLocaleString() + "',";
		sqlStr += "UserName = '" + user.getUsername() + "',";
		sqlStr += "UserDepartment = '" + user.getUserDepartment() + "',";
		sqlStr += "UserPhone = '" + user.getUserPhone() + "',";
		sqlStr += "UserEmail = '" + user.getUserEmail() + "',";
		sqlStr += "UserState = '" + user.getUserState() + "'";
		sqlStr += " WHERE UserId = ";
		sqlStr += "'" + user.getUserId() + "'";
		try {
			Statement stmt = StatementUtil.getStatement();
			stmt.executeUpdate(sqlStr);
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void delete(String id) {
		sqlStr = "";
		sqlStr += "DELETE FROM ac_user ";
		sqlStr += "WHERE UserId = '" + id + "'";
		try {
			Statement stmt = StatementUtil.getStatement();
			stmt.executeUpdate(sqlStr);
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public List findAll() {
		ResultSet rs;
		List result = new ArrayList();
		sqlStr = "SELECT * FROM ac_user order by UserDepartment";
		try {
			Statement stmt = StatementUtil.getStatement();
			rs = stmt.executeQuery(sqlStr);
			while (rs.next()) {
				AcUser ac_user = new AcUser();
				ac_user
						.setUserId(FormatUtil
								.ConvertUuid(rs.getBytes("UserId")));
				ac_user.setCallId((String) rs.getObject("CallId"));
				ac_user.setWorkerNO((String) rs.getObject("WorkerNo"));
				ac_user.setPassword((String) rs.getObject("Password"));
				ac_user.setExpiryDate((Date) rs.getObject("ExpiryDate"));
				ac_user.setUsername((String) rs.getObject("UserName"));
				ac_user.setUserDepartment(FormatUtil.ConvertUuid(rs
						.getBytes("UserDepartment")));
				ac_user.setUserPhone((String) rs.getObject("UserPhone"));
				ac_user.setUserEmail((String) rs.getObject("UserEmail"));
				ac_user.setUserState((Integer) rs.getObject("UserState"));
				result.add(ac_user);
			}
			rs.close();
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	public List search(AcUser user, List logic, List deptList) {
		List result = new ArrayList();
		sqlStr = "SELECT * FROM ac_user WHERE ";
		if (user.getUserId() != null) {
			sqlStr += "UserId LIKE '%" + user.getUserId() + "%' ";
		}
		sqlStr += logic.get(0) + " ";
		if (user.getCallId() != null) {
			sqlStr += "CallId LIKE '%" + user.getCallId() + "%' ";
		}
		/*
		 * sqlStr += logic.get(1)+" "; if(user.getExpiryDate()!=null){ sqlStr
		 * +="ExpiryDate LIKE '#"+user.getExpiryDate().toLocaleString()+"#' "; }
		 * else{ sqlStr +="ExpiryDate LIKE '##' "; }
		 */
		sqlStr += logic.get(2) + " ";
		if (user.getPassword() != null) {
			sqlStr += "Password LIKE '%" + user.getPassword() + "%' ";
		}
		sqlStr += logic.get(3) + " ";
		if (deptList.size() > 0) {
			sqlStr += "UserDepartment IN ('" + deptList.get(0) + "'";
			for (int i = 1; i < deptList.size(); i++) {
				sqlStr += ",'" + deptList.get(i) + "'";
			}
			sqlStr += ")";
		}
		sqlStr += logic.get(4) + " ";
		if (user.getUserEmail() != null) {
			sqlStr += "UserEmail LIKE '%" + user.getUserEmail() + "%' ";
		}
		sqlStr += logic.get(5) + " ";
		if (user.getUsername() != null) {
			sqlStr += "Username LIKE '%" + user.getUsername() + "%' ";
		}
		sqlStr += logic.get(6) + " ";
		if (user.getUserPhone() != null) {
			sqlStr += "UserPhone LIKE '%" + user.getUserPhone() + "%' ";
		}
		sqlStr += logic.get(7) + " ";
		if (user.getUserState() != null) {
			sqlStr += "UserState = " + user.getUserState() + " ";
		}
		sqlStr += logic.get(8) + " ";
		if (user.getWorkerNO() != null) {
			sqlStr += "WorkerNO LIKE '%" + user.getWorkerNO() + "%' ";
		}
		sqlStr += " order by UserDepartment";
		try {
			Statement stmt = StatementUtil.getStatement();
			ResultSet rs = stmt.executeQuery(sqlStr);
			while (rs.next()) {
				AcUser acUser = new AcUser();
				acUser.setUserId(FormatUtil.ConvertUuid(rs.getBytes("UserId")));
				acUser.setCallId((String) rs.getObject("CallId"));
				acUser.setWorkerNO((String) rs.getObject("WorkerNo"));
				acUser.setPassword((String) rs.getObject("Password"));
				acUser.setExpiryDate((Date) rs.getObject("ExpiryDate"));
				acUser.setUsername((String) rs.getObject("UserName"));
				acUser.setUserDepartment(FormatUtil.ConvertUuid(rs
						.getBytes("UserDepartment")));
				acUser.setUserPhone((String) rs.getObject("UserPhone"));
				acUser.setUserEmail((String) rs.getObject("UserEmail"));
				acUser.setUserState((Integer) rs.getObject("UserState"));
				result.add(acUser);
			}
			rs.close();
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
}